﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

using Data_Objects;
using Business_Objects;
namespace Data_Objects.Implementation
{
    //Ko public -> tất cả gọi qua dataacess
    class SqlServerNhanVienDao : INhanVienDao
    {
        public SqlServerNhanVienDao()
        {
            SQLUtility.TableName = "NhanVien";
        }

        //Trả về đối tượng báo ăn
        private NhanVien MakeNhanVien(DataRow row)
        {
            Guid _id = Guid.Parse(row["NhanVienID"].ToString());
            Guid _maPhongBan = Guid.Parse(row["MaPhongBan"].ToString());
            System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();

            byte[] _anh = encoding.GetBytes("Chưa có ảnh"); 
            //byte[] _anh = new byte[1000 * 1000 * 3];
            //if (row["Anh"] != null)
            //{
            //    _anh = (byte[])row["Anh"];
            //}

            //byte[] _anh = (byte[])row["Anh"];
            string _soNhanVien = row["SoNhanVien"].ToString();
            string _biDanh = row["BiDanh"].ToString();
            string _hoTen = row["HoTen"].ToString();
            DateTime _ngaySinh = DateTime.Parse(row["NgaySinh"].ToString());
            bool _gioiTinh = bool.Parse(row["GioiTinh"].ToString());
         string _danToc = row["DanToc"].ToString();
         string _tonGiao = row["TonGiao"].ToString();
         string _quocTich = row["QuocTich"].ToString();
         string _soCMND = row["SoCMND"].ToString();
            DateTime _ngayCap = new DateTime();
            if (row["NgayCap"].ToString()!="")
            {
                 _ngayCap = DateTime.Parse(row["NgayCap"].ToString());
            }
        
         string _noiCap = row["NoiCap"].ToString();
            int _mobile = 0;
         if (row["Mobile"].ToString()!="")
            {
                 _mobile = int.Parse(row["Mobile"].ToString());
            }
        
         string _email = row["Email"].ToString();
         Guid _trinhDo = Guid.Parse(row["TrinhDo"].ToString());
         string _heDaoTao = row["HeDaoTao"].ToString();
         string _chucVu = row["ChucVu"].ToString();
         bool _datChuanNgheNghiep = bool.Parse(row["DatChuanNgheNghiep"].ToString());
         string _diaChiThuongChu = row["DiaChiThuongChu"].ToString();
         string _tinhThuongChu = row["TinhThuongChu"].ToString();
         string _huyenThuongChu = row["HuyenThuongChu"].ToString();
         string _xaThuongChu = row["XaThuongChu"].ToString();
         string _hoTenBo = row["HoTenBo"].ToString();
         string _hoTenMe = row["HoTenMe"].ToString();
         string _hoTenVoChong = row["HoTenVoChong"].ToString();
         bool _voChongConSong = bool.Parse(row["VoChongConSong"].ToString());
         bool _laDoanVien = bool.Parse(row["LaDoanVien"].ToString());
            DateTime _ngayVaoDoan = new DateTime();
         if (row["NgayVaoDoan"].ToString()!="")
            {
                 _ngayVaoDoan = DateTime.Parse(row["NgayVaoDoan"].ToString());
            }
         
         bool _laDangVien = bool.Parse(row["LaDangVien"].ToString());
            DateTime _ngayVaoDang = new DateTime();
         if (row["NgayVaoDang"].ToString() != "")
         {
              _ngayVaoDang = DateTime.Parse(row["NgayVaoDang"].ToString());
         }
        
         string _soTheDang = row["SoTheDang"].ToString();

         string _loaiHopDong = row["LoaiHopDong"].ToString();
         string _soSoBaoHiem = row["SoSoBaoHiem"].ToString();
         bool _laDanTocThieuSo = bool.Parse(row["LaDanTocThieuSo"].ToString());
         decimal _heSoLuong = decimal.Parse(row["HeSoLuong"].ToString());
         decimal _bacLuong = decimal.Parse(row["BacLuong"].ToString());
            DateTime _ngayNghiViec=new DateTime( );
         if (row["NgayNghiViec"].ToString() != "")
         {
              _ngayNghiViec = DateTime.Parse(row["NgayNghiViec"].ToString());
         }
       
         string _lyDoNghiViec = row["LyDoNghiViec"].ToString();
         int _schoolID = int.Parse(row["SchoolID"].ToString());



         return new NhanVien(_id, _maPhongBan, _anh, _soNhanVien, _biDanh, _hoTen, _ngaySinh, _gioiTinh, _danToc, _tonGiao,_quocTich,_soCMND,_ngayCap,_noiCap,_mobile,_email,_trinhDo,_heDaoTao,_chucVu,_datChuanNgheNghiep,_diaChiThuongChu,_tinhThuongChu,_huyenThuongChu,_xaThuongChu,_hoTenBo,_hoTenMe,_hoTenVoChong,_voChongConSong,_laDoanVien,_ngayVaoDoan,_laDangVien,_ngayVaoDang,_soTheDang,_loaiHopDong,_soSoBaoHiem,_laDanTocThieuSo,_heSoLuong,_bacLuong,_ngayNghiViec,_lyDoNghiViec,_schoolID);
        }

        //Lấy danh sách khách hàng trả về datatable
        private IList<NhanVien> MakeNhanViens(DataTable dt)  
        {
            IList<NhanVien> list = new List<NhanVien>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(MakeNhanVien(row));
            }
            return list;
        }
        private DbParameter[] Take(NhanVien NhanVien)
        {
            DbParameter[] parameters = new DbParameter[41];
            parameters[0] = new SqlParameter("@NhanVienID", NhanVien.Id);
            parameters[1] = new SqlParameter("@MaPhongBan", NhanVien.MaPhongBan);
            parameters[2] = new SqlParameter("@Anh", NhanVien.Anh);
            parameters[3] = new SqlParameter("@SoNhanVien", NhanVien.SoNhanVien);
            parameters[4] = new SqlParameter("@BiDanh", NhanVien.BiDanh);

            parameters[5] = new SqlParameter("@HoTen", NhanVien.HoTen);
            parameters[6] = new SqlParameter("@NgaySinh", NhanVien.NgaySinh);
            parameters[7] = new SqlParameter("@GioiTinh", NhanVien.GioiTinh);
            parameters[8] = new SqlParameter("@DanToc", NhanVien.DanToc);

            parameters[9] = new SqlParameter("@TonGiao", NhanVien.TonGiao);
            parameters[10] = new SqlParameter("@QuocTich", NhanVien.QuocTich);
            parameters[11] = new SqlParameter("@SoCMND", NhanVien.SoCMND);
            parameters[12] = new SqlParameter("@NgayCap", NhanVien.NgayCap);

            parameters[13] = new SqlParameter("@NoiCap", NhanVien.NoiCap);
            parameters[14] = new SqlParameter("@Mobile", NhanVien.Mobile);
            parameters[15] = new SqlParameter("@Email", NhanVien.Email);
            parameters[16] = new SqlParameter("@TrinhDo", NhanVien.TrinhDo);

            parameters[17] = new SqlParameter("@HeDaoTao", NhanVien.HeDaoTao);
            parameters[18] = new SqlParameter("@ChucVu", NhanVien.ChucVu);
            parameters[19] = new SqlParameter("@DatChuanNgheNghiep", NhanVien.DatChuanNgheNghiep);
            parameters[20] = new SqlParameter("@DiaChiThuongChu", NhanVien.DiaChiThuongChu);

            parameters[21] = new SqlParameter("@TinhThuongChu", NhanVien.TinhThuongChu);
            parameters[22] = new SqlParameter("@HuyenThuongChu", NhanVien.HuyenThuongChu);
            parameters[23] = new SqlParameter("@XaThuongChu", NhanVien.XaThuongChu);
            parameters[24] = new SqlParameter("@HoTenBo", NhanVien.HoTenBo);

            parameters[25] = new SqlParameter("@HoTenMe", NhanVien.HoTenMe);
            parameters[26] = new SqlParameter("@HoTenVoChong", NhanVien.HoTenVoChong);
            parameters[27] = new SqlParameter("@VoChongConSong", NhanVien.VoChongConSong);
            parameters[28] = new SqlParameter("@LaDoanVien", NhanVien.LaDoanVien);


            parameters[29] = new SqlParameter("@NgayVaoDoan", NhanVien.NgayVaoDoan);
            parameters[30] = new SqlParameter("@LaDangVien", NhanVien.LaDangVien);
            parameters[31] = new SqlParameter("@NgayVaoDang", NhanVien.NgayVaoDang);
            parameters[32] = new SqlParameter("@SoTheDang", NhanVien.SoTheDang);


            parameters[33] = new SqlParameter("@LoaiHopDong", NhanVien.LoaiHopDong);
            parameters[34] = new SqlParameter("@SoSoBaoHiem", NhanVien.SoSoBaoHiem);
            parameters[35] = new SqlParameter("@LaDanTocThieuSo", NhanVien.LaDanTocThieuSo);
            parameters[36] = new SqlParameter("@HeSoLuong", NhanVien.HeSoLuong);


            parameters[37] = new SqlParameter("@BacLuong", NhanVien.BacLuong);
            parameters[38] = new SqlParameter("@NgayNghiViec", NhanVien.NgayNghiViec);
            parameters[39] = new SqlParameter("@LyDoNghiViec", NhanVien.LyDoNghiViec);
            parameters[40] = new SqlParameter("@SchoolID", NhanVien.SchoolID);
            return parameters;
        }

        public IList<Business_Objects.NhanVien> GetNhanVien()
        {
            SQLUtility.TableName = "NhanVien";
            DbParameter[] param = null;
            DataTable dt = CommandProvider.GetDatatable(SQLUtility.ProceduresGetAll, param);
            return MakeNhanViens(dt); 
        }

        public Business_Objects.NhanVien GetNhanVienByID(string NhanVienID)
        {
            SQLUtility.TableName = "NhanVien";
            DbParameter[] parameters = new DbParameter[1];
            parameters[0] = new SqlParameter("@NhanVienID", NhanVienID);
            DataRow row = CommandProvider.GetDataRow(SQLUtility.ProceduresGetByID, parameters);
            if (row == null) { return null; }
            else
            {
                return MakeNhanVien(row);
            }
        }

        public void InsertNhanVien(Business_Objects.NhanVien NhanVien)
        {
            SQLUtility.TableName = "NhanVien";
            int newID = CommandProvider.Insert(SQLUtility.ProceduresInsert, Take(NhanVien), true);
        }

        public int UpdateNhanVien(Business_Objects.NhanVien NhanVien)
        {
            SQLUtility.TableName = "NhanVien";
            try { return CommandProvider.Update(SQLUtility.ProceduresUpdate, Take(NhanVien)); }
            catch { return -1; }
        }

        public int DeleteNhanVien(Business_Objects.NhanVien NhanVien)
        {
            SQLUtility.TableName = "NhanVien";
            try { return CommandProvider.Delete(SQLUtility.ProceduresDelete, Take(NhanVien)); }
            catch { return -1; }
        }


        public List<NhanVien> GetNhanVienBySchoolId(int schoolId)
        {
            DbParameter[] parameters = new DbParameter[1];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            DataTable dt = CommandProvider.GetDatatable("usp_SelectNhanViensByAndSchoolID", parameters);
            return MakeNhanViens(dt).ToList();  
        }


        public List<NhanVien> GetGiaoVienBySchoolIdAndSchoolYearIDNotPhanLop(int schoolId)
        {
            DbParameter[] parameters = new DbParameter[1];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
        
            DataTable dt = CommandProvider.GetDatatable("usp_SelectPhanCongGiangDayByAndSchoolYearIDAndSchoolIDAndNotPhanCong", parameters);
            return MakeNhanViens(dt).ToList(); 
        }
    }
}
